# 检测模块 - 包含所有数据检测功能
from config import load_env_structure_dx, load_env_structure_setl, load_env_tbl_name, load_env_structure_mdtrt
from sqlalchemy import text

# 获取列名和表名
SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME = load_env_tbl_name()
(SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, SETL_SETL_TIME,
 SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG) = load_env_structure_setl()
(DX_SETL_ID, DX_DIAG_TYPE, DX_IS_MAIN, DX_DX_CODE, DX_DX_NAME, DX_VALIDFLAG) = load_env_structure_dx()
(
    MDTRT_MDTRT_ID, MDTRT_IN_OUT_ID, MDTRT_HSP_ID, MDTRT_HSP_NAME,
    MDTRT_HSP_LV, MDTRT_PSN_NO, MDTRT_CERTNO, MDTRT_CERT_TYPE,
    MDTRT_PSN_NAME, MDTRT_GEND0, MDTRT_BRDY, MDTRT_AGE,
    MDTRT_INSUTYPE0, MDTRT_IN_TIME, MDTRT_OUT_TIME,
    MDTRT_IN_DEPT, MDTRT_OUT_DEPT, MDTRT_VALIDFLAG,
    MDTRT_OUT_DX_CODE_CHECK, MDTRT_OUT_DX_NAME_CHECK,
    MDTRT_OUTP_DX_CHECK
) = load_env_structure_mdtrt()

def inspect_d01_diagnosis_main_count(engine, check_results, setl_count):
    """D01: 测试诊断表中每个SETL_ID的主诊断数量"""
    print('============ D01：测试诊断表中每个SETL_ID的主诊断数量 ============')

    sql = f"""
    SELECT {DX_SETL_ID}, COUNT(DISTINCT {DX_IS_MAIN}) AS main_dx_count
    FROM {DX_TBL_NAME} dx
    WHERE EXISTS(
        SELECT *
        FROM {SETL_TBL_NAME} m
        WHERE m.{SETL_SETL_ID} = dx.{DX_SETL_ID}
        AND m.{SETL_VALIDFLAG} = '1'
    )
    AND {DX_VALIDFLAG} = '1'
    GROUP BY {DX_SETL_ID}
    HAVING COUNT(DISTINCT {DX_IS_MAIN})  != 1
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql))
        invalid_dx_setl_ids = result.fetchall()

    # if invalid_dx_setl_ids:
    #     print("诊断表中每个SETL_ID的主诊断数量不是1的:")
    #     for row in invalid_dx_setl_ids[:20]:
    #         print(f"SETL_ID: {row[0]}, 主诊断数量: {row[1]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA 
    WITH T0 AS (
        SELECT {DX_SETL_ID}, COUNT(CASE WHEN {DX_IS_MAIN}='1' THEN {DX_DX_CODE} ELSE NULL END) AS main_dx_count
        FROM {DX_TBL_NAME} dx
        WHERE EXISTS(
            SELECT *
            FROM {SETL_TBL_NAME} m
            WHERE m.{SETL_SETL_ID} = dx.{DX_SETL_ID}
            AND m.{SETL_VALIDFLAG} = '1'
        )
        AND {DX_VALIDFLAG} = '1'
        GROUP BY {DX_SETL_ID}
        HAVING COUNT(CASE WHEN {DX_IS_MAIN}='1' THEN {DX_DX_CODE} ELSE NULL END) != 1
    )
    SELECT 
        {DX_SETL_ID}, 
        'D01', 
        '诊断表中IS_MAIN数量不是1', 
        2,
        '主诊断数量: ' || main_dx_count
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"D01，诊断表中setl_id的主诊断数量不是1的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "D01",
        "inspect_name": "诊断表中每个SETL_ID的主诊断数量不是1",
        "sql": sql,
        "msg": "主诊断数量不是1的SETL_ID",
        "info": [{"SETL_ID": row[0], "主诊断数量": row[1]} for row in invalid_dx_setl_ids[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })

    print('============ D01：测试诊断表中每个SETL_ID的主诊断数量 FINISHED ============')

# 检测主单表的SETL_ID有多少不在诊断表的SETL_ID中
def inspect_d02_setlid_not_in(engine, check_results, setl_count):
    print('============ D02：检测主单表的SETL_ID有多少不在诊断表的SETL_ID中 ============')

    sql_missing_setl_id = f"""
    SELECT {SETL_SETL_ID}
    FROM {SETL_TBL_NAME}
    WHERE {SETL_SETL_ID} NOT IN (
        SELECT DISTINCT {DX_SETL_ID} FROM {DX_TBL_NAME} WHERE {DX_VALIDFLAG} = '1'
    )
    AND {SETL_VALIDFLAG} = '1'
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql_missing_setl_id))
        missing_setl_ids = result.fetchmany(20)

    # if missing_setl_ids:
    #     print("主单表中以下SETL_ID未在诊断表中出现:")
    #     for row in missing_setl_ids[:20]:
    #         print(f"SETL_ID: {row[0]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA 
    WITH T0 AS (
        SELECT {SETL_SETL_ID}
        FROM {SETL_TBL_NAME}
        WHERE {SETL_SETL_ID} NOT IN (
            SELECT DISTINCT {DX_SETL_ID} FROM {DX_TBL_NAME} WHERE {DX_VALIDFLAG} = '1'
        )
        AND {SETL_VALIDFLAG} = '1'
    )
    SELECT 
        {SETL_SETL_ID},
        'D02',
        '主单表SETL_ID未在诊断表中出现',
        2,
        {SETL_SETL_ID}
    FROM T0
    """
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"D02，主单表SETL_ID未在诊断表中出现的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "D02",
        "inspect_name": "主单表SETL_ID未在诊断表中出现",
        "sql": sql_missing_setl_id,
        "msg": "主单表SETL_ID未在诊断表中出现",
        "info": [{"SETL_ID": row[0]} for row in missing_setl_ids[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })
    
    print('============ D02：检测主单表的SETL_ID有多少不在诊断表的SETL_ID中 FINISHED ============')

# 检测主单表的SETL_ID有多少不在诊断表的SETL_ID中，而且在就诊表中的出入院诊断、门诊诊断中都不存在
# MDTRT_IN_DX_NAME_CHECK=ADM_DIAG_DSCR
# MDTRT_OUT_DX_CODE_CHECK=DSCG_MAINDIAG_CODE
# MDTRT_OUT_DX_NAME_CHECK=DSCG_MAINDIAG_NAME
# # 门诊诊断
# MDTRT_OUTP_DX_CHECK=OTP_DIAG_INFO
def inspect_d03_setlid_not_in_mdtrt(engine, check_results, setl_count):
    print('============ D03：检测主单表的SETL_ID有多少不在就诊表的出入院诊断、门诊诊断中 ============')

    sql_missing_setl_id = f"""
    SELECT DISTINCT setl.{SETL_SETL_ID}
    FROM {SETL_TBL_NAME} setl, {MDTRT_TBL_NAME} mdtrt
    WHERE setl.{SETL_MDTRT_ID} = mdtrt.{MDTRT_MDTRT_ID}
    AND mdtrt.{MDTRT_VALIDFLAG} = '1'
    AND setl.{SETL_VALIDFLAG} = '1'
    AND mdtrt.{MDTRT_OUT_DX_CODE_CHECK} IS NULL
    AND mdtrt.{MDTRT_OUT_DX_NAME_CHECK} IS NULL
    AND mdtrt.{MDTRT_OUTP_DX_CHECK} IS NULL
    AND NOT EXISTS (
        SELECT 1
        FROM {DX_TBL_NAME} dx
        WHERE dx.{DX_SETL_ID} = setl.{SETL_SETL_ID}
        AND dx.{DX_VALIDFLAG} = '1'
    )
    """

    with engine.connect() as connection:
        result = connection.execute(text(sql_missing_setl_id))
        missing_setl_ids = result.fetchmany(20)

    # if missing_setl_ids:
    #     print("主单表中以下SETL_ID未在就诊表的出入院诊断、门诊诊断中出现:")
    #     for row in missing_setl_ids[:20]:
    #         print(f"SETL_ID: {row[0]}")
    
    # 将检测结果导入 dirty_data 表 (SETL_ID, INSPECT_ID, INSPECT_NAME, LV, INFO)
    sqlIns = f"""
    INSERT INTO DIRTY_DATA 
    WITH T0 AS (
        SELECT DISTINCT setl.{SETL_SETL_ID}
        FROM {SETL_TBL_NAME} setl, {MDTRT_TBL_NAME} mdtrt
        WHERE setl.{SETL_MDTRT_ID} = mdtrt.{MDTRT_MDTRT_ID}
        AND mdtrt.{MDTRT_VALIDFLAG} = '1'
        AND setl.{SETL_VALIDFLAG} = '1'
        AND mdtrt.{MDTRT_OUT_DX_CODE_CHECK} IS NULL
        AND mdtrt.{MDTRT_OUT_DX_NAME_CHECK} IS NULL
        AND mdtrt.{MDTRT_OUTP_DX_CHECK} IS NULL
        AND NOT EXISTS (
            SELECT 1
            FROM {DX_TBL_NAME} dx
            WHERE dx.{DX_SETL_ID} = setl.{SETL_SETL_ID}
            AND dx.{DX_VALIDFLAG} = '1'
        )
    )
    SELECT 
        {SETL_SETL_ID},
        'D03',
        '主单表SETL_ID没有诊断',
        2,
        {SETL_SETL_ID}
    FROM T0
    """
    
    with engine.connect() as connection:
        result_ins = connection.execute(text(sqlIns))
        inserted_count = result_ins.rowcount if hasattr(result_ins, 'rowcount') else None
        print(f"D03，主单表SETL_ID未在就诊表的出入院诊断、门诊诊断中出现的记录: {inserted_count}")
        print(f"占主单表总数量的比例：{inserted_count / setl_count * 100:.2f}%" if setl_count > 0 else "占主单表总数量的比例：0%")
        connection.commit()

    check_results.append({
        "inspect_id": "D03",
        "inspect_name": "主单表SETL_ID未在就诊表的出入院诊断、门诊诊断中出现",
        "sql": sql_missing_setl_id,
        "msg": "主单表SETL_ID未在就诊表的出入院诊断、门诊诊断中出现",
        "info": [{"SETL_ID": row[0]} for row in missing_setl_ids[:20]],
        "count": inserted_count if inserted_count is not None else 0,
        "ocp": inserted_count / setl_count * 100 if setl_count > 0 else 0
    })